# -*- coding:utf-8 -*-


class UserManageSql(object):
    """用户管理相关SQL"""
    # 所有成员
    ALL_USER = "SELECT id, user_name FROM user_info"

    # 用户登录
    USER_LOGIN = "SELECT * FROM user_info WHERE user_name = %s AND user_passwd = %s AND user_status = 0"

    # 创建用户
    CREAT_USER = "INSERT INTO user_info(user_name, user_passwd, creat_time, user_status, role_id,company_post,department) " \
                 "VALUES (%s, %s, %s, %s, %s,%s,%s)"

    # 查看用户是否是超级管理员
    USR_IS_ADMIN = "SELECT role_name FROM user_info " \
                   "LEFT JOIN role " \
                   "ON role.id = user_info.role_id " \
                   "WHERE user_info.id = %s"

    # 删除用户
    DELETE_USR = "UPDATE user_info SET user_status = 1 WHERE id= %s "

    # 查询用户是否存在
    QUERY_USER = "SELECT * FROM user_info WHERE user_name = %s "

    # 查询用户是否存在
    QUERY_USER_PASS = "SELECT * FROM user_info WHERE user_name = %s and user_passwd=%s and id=%s"

    # 重置密码
    RESRT_PASSWD = "UPDATE user_info SET user_passwd = %s, update_time = %s WHERE id = %s"

    # 用户列表查看
    USER_LIST = "SELECT user_info.id, user_name, creat_time, role_name, role.id role_id " \
                "FROM user_info " \
                "LEFT JOIN role " \
                "ON user_info.role_id = role.id " \
                "WHERE user_status = 0"

    # 用户权限列表查看
    USER_PEIMIT_LIST = "SELECT menu.id menu_id, menu_name,second_menu_name, menu.menu_url, sm.second_menu_url " \
                       "FROM user_permit up " \
                       "LEFT JOIN menu " \
                       "ON up.menu_id = menu.id " \
                       "LEFT JOIN second_menu sm " \
                       "ON menu.id = sm.menu_id " \
                       "WHERE up.user_id = %s " \
                       "AND up.is_use = 0 " \
                       "ORDER BY menu_id "

# 查询用户权限列表
# "SELECT menu_name,second_menu_name, menu.menu_url, sm.second_menu_url  " \
# "FROM user_permit_second ups " \
# "LEFT JOIN menu " \
# "ON ups.menu_id = menu.id " \
# "LEFT JOIN second_menu sm " \
# "ON ups.second_menu_id = sm.id " \
# "WHERE user_id = 4 "

    # 删除一级菜单权限
    UPDATE_ONE_PERMIT = "UPDATE user_permit SET is_use = 0 WHERE user_id = %s AND menu_id = %s"

    # 查询用户权限
    QUERY_PERMIT = "SELECT * FROM user_permit WHERE user_id = %s AND menu_id = %s"

    # 添加用户权限
    ADD_PERMIT = "INSERT INTO user_permit (user_id, menu_id) VALUES (%s, %s)"

    # 删除用户权限
    DELETE_PERMIT = "DELETE FROM user_permit WHERE user_id = %s AND menu_id = %s "

    # 权限列表
    PERMIT_LIST = "SELECT * FROM menu"

    # 用户对应权限列表
    USER_TO_PERMIT_LIST = "SELECT ui.user_name, menu_name " \
                          "FROM user_info ui " \
                          "LEFT JOIN user_permit up " \
                          "ON up.user_id = ui.id " \
                          "LEFT JOIN menu " \
                          "ON up.menu_id = menu.id " \
                          "WHERE up.is_use = 0"

    # 查询用户旧密码是否正确
    SELECT_OLD_PASSWD = "SELECT * FROM user_info WHERE user_name = %s AND user_passwd = %s"

    # 修改密码
    UPDATE_PASSWD = "UPDATE user_info SET user_passwd = %s, update_time = %s WHERE user_name = %s"

    # 用户权限
    SHOW_CHECKED = "SELECT menu_name, menu.id  FROM user_permit up " \
                   "LEFT JOIN menu ON up.menu_id = menu.id " \
                   "WHERE up.user_id = %s " \
                   "AND is_use = 0"
    # 栏目
    CLOUM = "SELECT id, menu_name, false as checked FROM menu "

    # 修改全部权限不可用
    UPDATE_ONE = "UPDATE user_permit SET is_use = 1 WHERE user_id = %s"